const path = require('path')
const fs = require('fs')
const xlsx = require('xlsx')
const { columnExcelMap, columnExcelSort, excelColumnMap } = require('./column-map')
const { sequelize } = require('../models')

let commonSql = 'select '
columnExcelSort.forEach((x) => {
  if (x === 'days31InAim') {
    commonSql += `A."${x}" as 过${columnExcelMap[x]}, \n` // sql的字段名不允许以数字开头，所以加个字符在前面
  } else {
    commonSql += `A."${x}" as ${columnExcelMap[x]}, \n`
  }
})
commonSql += 'A."createAt" as 导入时间 from "History" as A where 1=1'

module.exports = {
  page: async (ctx) => {
    const { currentPage, pageSize, code, createStart, createEnd } = ctx.request.body
    let sql = 'select A.*, B."createAt" commentAt from "History" A left join "Comment" B on A.code=B.code where 1=1'
    let countSql = 'select count(1) as total from "History" A where 1=1' // 计算总数
    let replacements = {}
    let where = ''
    if (code) {
      where += ` and A.code like :code`
      replacements.code = `%${code}%`
    }
    if (createStart) {
      where += ` and A."createAt">=:createStart`
      replacements.createStart = createStart
    }
    if (createEnd) {
      where += ` and A."createAt"<=:createEnd`
      replacements.createEnd = createEnd
    }
    sql += where
    countSql += where
    sql += ` limit ${pageSize} offset ${(currentPage - 1) * pageSize};`
    try {
      const [pageData] = await sequelize.query(sql + countSql, { replacements, raw: true })

      ctx.body = {
        code: 200,
        data: pageData,
        msg: '获取分页数据成功'
      }
    } catch (error) {
      console.log('获取分页数据失败：', error)
      ctx.body = {
        code: 500,
        data: null,
        msg: '获取分页数据失败'
      }
    }
  },
  exportExcel: async (ctx) => {
    console.log('请求的数据', ctx.request.body)
    const params = ctx.request.body
    let execSql = commonSql
    let excelTitle = '导出所有历史数据(无点评).xlsx'
    let replacements = {}
    let where = ''
    if ('code' in params) {
      // 通过查询条件判断是否有code字段，如果有则说明是导出所有的查询历史数据
      if (params.code) {
        where += ` and A.code like :code`
        replacements.code = `%${params.code}%`
      }
      excelTitle = '导出所有查询(无点评).xlsx'
      const { createStart, createEnd } = params
      if (createStart) {
        if (createStart) {
          where += ` and A."createAt">=:createStart`
          replacements.createStart = createStart
        }
        if (createEnd) {
          where += ` and A."createAt"<=:createEnd`
          replacements.createEnd = createEnd
        }
      }
    }
    const [results] = await sequelize.query(execSql + where, { replacements, raw: true })
    const ws = xlsx.utils.json_to_sheet(results)
    const wb = xlsx.utils.book_new() // 创建一个空的workbook
    xlsx.utils.book_append_sheet(wb, ws, 'Sheet1')
    xlsx.writeFileSync(wb, excelTitle)

    //类型
    ctx.type = '.xlsx'
    //请求返回，生成的xlsx文件
    ctx.body = fs.readFileSync(excelTitle)
    //请求返回后，删除生成的xlsx文件，不删除也行，下次请求回覆盖
    fs.unlink(excelTitle, (err) => {
      if (err) {
        console.log(err)
        throw err
      }
    })
  }
}
